﻿Imports SIGI.Common.BE
Imports System.Data.SqlClient
Imports System.Configuration

Public Class CountryDAC

#Region "<< -Search- >>"
    Public Shared Function Search() As List(Of CountryBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty

        Dim wCountryList As List(Of CountryBE) = Nothing
        Dim wCountryBE As CountryBE = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Pais_s"

            'Abro la conexión a la BD
            wSqlConn.Open()

            wCountryList = New List(Of CountryBE)
            wReader = wSqlCmd.ExecuteReader()
            While wReader.Read
                wCountryBE = New CountryBE

                wCountryBE.Id = Convert.ToInt32(wReader("Id_Pais"))
                wCountryBE.Description = Convert.ToString(wReader("Descripcion"))

                wCountryList.Add(wCountryBE)
            End While

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        Finally
            wReader = Nothing
            wSqlCmd = Nothing
            wSqlConn = Nothing
        End Try

        Return wCountryList
    End Function
#End Region

#Region "<< -SearchProvinceByCountryId- >>"
    Public Shared Function SearchProvinceByCountryId(pCountryId As Integer) As List(Of ProvinceBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty

        Dim wProvinceList As List(Of ProvinceBE) = Nothing
        Dim wProvinceBE As ProvinceBE = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Provicias_s_ByPaisId"

            wSqlCmd.Parameters.Add("@PaisId", SqlDbType.Int)
            wSqlCmd.Parameters("@PaisId").Value = pCountryId

            'Abro la conexión a la BD
            wSqlConn.Open()

            wProvinceList = New List(Of ProvinceBE)
            wReader = wSqlCmd.ExecuteReader()
            While wReader.Read
                wProvinceBE = New ProvinceBE

                wProvinceBE.IdCountry = pCountryId
                wProvinceBE.Id = Convert.ToInt32(wReader("Id_Provincia"))
                wProvinceBE.Description = Convert.ToString(wReader("Descripcion"))

                wProvinceList.Add(wProvinceBE)
            End While

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        Finally
            wReader = Nothing
            wSqlCmd = Nothing
            wSqlConn = Nothing
        End Try

        Return wProvinceList
    End Function
#End Region

#Region "<< -SearchCityByProvinceId- >>"
    Public Shared Function SearchCityByProvinceId(pProvinceId As Integer) As List(Of CityBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty

        Dim wCityList As List(Of CityBE) = Nothing
        Dim wCityBE As CityBE = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Ciudades_s_ByProvinciaId"

            wSqlCmd.Parameters.Add("@ProvinciaId", SqlDbType.Int)
            wSqlCmd.Parameters("@ProvinciaId").Value = pProvinceId

            'Abro la conexión a la BD
            wSqlConn.Open()

            wCityList = New List(Of CityBE)
            wReader = wSqlCmd.ExecuteReader()
            While wReader.Read
                wCityBE = New CityBE

                wCityBE.IdProvince = pProvinceId
                wCityBE.Id = Convert.ToInt32(wReader("Id_Ciudad"))
                wCityBE.Description = Convert.ToString(wReader("Descripcion"))

                wCityList.Add(wCityBE)
            End While

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        Finally
            wReader = Nothing
            wSqlCmd = Nothing
            wSqlConn = Nothing
        End Try

        Return wCityList
    End Function
#End Region

End Class
